﻿using DocumentFormat.OpenXml.Wordprocessing;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace black_screen_mode_server.Models
{
    public class ListInfoSqlHelper
    {
        public static void AddListInfo(ListInfo listInfo, int listType)
        {
            try
            {
               string sql = $"insert into list(id,GroupName, AreaName, Category,EventType,Block,Source, ListType) values({listInfo.Id},'{listInfo.GroupName}','{listInfo.AreaName}',{listInfo.Category}," +
               $"'{listInfo.EventType}','{listInfo.Block}','{listInfo.Source}',{listType}) ";
                SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
                helper.ExecuteNonQuery(sql);
            }
            catch (Exception)
            {
                Logger.Singleton.Error("添加名单信息失败！");
            }
           
        }

        public static int GetMaxId()
        {
            //int maxId = 0;
            //try
            //{
            //    string sql = "select max(Id) from dbo.list";
            //    SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
            //    DataTable dt = helper.GetByTable(sql);
            //    Logger.Singleton.Info("@1获取到list 最大ID：" + dt.Rows.Count);
            //    if (dt.Rows.Count == 0)
            //    {
            //        Logger.Singleton.Info("@2获取到list 最大ID：0");
            //        return 0;
            //    }
            //    maxId = 

            //}
            //catch (Exception)
            //{
            //    return 0;
            //}

            string emptySql = $"select * from dbo.list";
            SqlHelper emptyHelper = new SqlHelper(ProjectTools.configDbConnString);
            DataTable emptyDt = emptyHelper.GetByTable(emptySql);
            if(emptyDt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                string sql = "select max(Id) from dbo.list";
                SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
                DataTable dt = helper.GetByTable(sql);
                return Convert.ToInt32(dt.Rows[0][0]);
            }

            //string sql = "select max(Id) from dbo.list";
            //SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
            //DataTable dt = helper.GetByTable(sql);
            //Logger.Singleton.Info("@1获取到list 最大ID：" + dt.Rows.Count);
            //if (dt.Rows.Count == 0)
            //{
            //    Logger.Singleton.Info("@2获取到list 最大ID：0");
            //    return 0;
            //}
            //Logger.Singleton.Info("@3获取到list 最大ID：" + Convert.ToInt32(dt.Rows[0][0]));
            //return Convert.ToInt32(dt.Rows[0][0]);
        }

        public static void DeleteListInfo(int id, int listType)
        {
            try
            {
                string sql = $"delete from list where Id ={id}";
                SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
                helper.ExecuteNonQuery(sql);
            }
            catch (Exception)
            {

                Logger.Singleton.Error("删除名单信息失败！");
            }
           
        }

        public static void UpdateListInfo(int id, ListInfo listInfo, int listType)
        {
            try
            {
                string sql = $"update dbo.list set Id ={listInfo.Id}, GroupName ='{listInfo.GroupName}', AreaName ='{listInfo.AreaName}', Category = {listInfo.Category}, " +
             $"EventType ='{listInfo.EventType}', Block='{listInfo.Block}', Source = '{listInfo.Source}', ListType ={listType}" +
             $" where Id = {id}";
                SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
                helper.ExecuteNonQuery(sql);
            }
            catch (Exception)
            {

                Logger.Singleton.Error("更新名单信息失败！");
            }
         
        }

        public static List<ListInfo> GetListInfo(int listType)
        {
            string sql = $"select * from dbo.list where ListType ={listType}";
            SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
            DataTable dt = helper.GetByTable(sql);

            List<ListInfo> list = new List<ListInfo>();
            try
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ListInfo listInfo = new ListInfo();
                    listInfo.Id = Convert.ToInt32(dr["Id"]);
                    listInfo.GroupName = dr["GroupName"].ToString();
                    listInfo.Category = Convert.ToInt32(dr["Category"]);
                    listInfo.EventType = dr["EventType"].ToString();
                    listInfo.Block = dr["Block"].ToString();
                    listInfo.Source = dr["Source"].ToString();
                    listInfo.AreaName = dr["AreaName"].ToString();

                    list.Add(listInfo);
                }
            }
            catch (Exception)
            {

                Logger.Singleton.Error("获取名单信息失败！");
            }


            return list;
        }

        public static List<ListInfo> GetListInfo(int listType, int pageIndex, int pageSize)
        {
            string sql = $"select * from dbo.list where ListType ={listType} order by Id " +
               $"OFFSET {pageIndex * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";
            SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
            DataTable dt = helper.GetByTable(sql);

            List<ListInfo> list = new List<ListInfo>();
            try
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ListInfo listInfo = new ListInfo();
                    listInfo.Id = Convert.ToInt32(dr["Id"]);
                    listInfo.GroupName = dr["GroupName"].ToString();
                    listInfo.Category = Convert.ToInt32(dr["Category"]);
                    listInfo.EventType = dr["EventType"].ToString();
                    listInfo.Block = dr["Block"].ToString();
                    listInfo.Source = dr["Source"].ToString();
                    listInfo.AreaName = dr["AreaName"].ToString();

                    list.Add(listInfo);
                }
            }
            catch (Exception)
            {

                Logger.Singleton.Error("获取名单信息失败！");
            }


            return list;
        }

        public static int GetListInfoCount(int listType)
        {
            try
            {
                string sql = $"select count(*) from dbo.list where ListType ={listType}";
                SqlHelper helper = new SqlHelper(ProjectTools.configDbConnString);
                int count = helper.ExecuteScalar(sql);
                return count;
            }
            catch (Exception)
            {
                return 0;
            }
          
          
        }

    }
}
